#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sys
import csv
import sqlite3
from operator import itemgetter



dataList = []

#Create a data base with a single file CSV
def dataBase(fileName):
         
   spamReader = csv.reader(open('reb{0}.csv'.format(str(fileName)),\
                           'rb'),delimiter=',' )

   connect = sqlite3.connect("lp_master.db")
   c = connect.cursor()
   if fileName == 0:
      c.execute('''drop table if exists teachers ''') 
      c.execute('''create table teachers (Codigo_do_Docente
                  integer, Nome text, Ultimo_Grau text, Curso text,
                  Categoria text, Regime_de_Tempo text, Ano_Lectivo text,
                  Tipo_de_Estabelecimento text, Nome_do_Estabelecimento
                  text)''')
      pass
   spamReader.next()
   for row in spamReader:
      c.execute('''insert into teachers values ("{0}", "{1}", "{2}",
                  "{3}", "{4}", "{5}", "{6}", "{7}",
                  "{8}")'''.format(row[0].replace('"', '\''),\
                  row[1].replace('"', '\''),row[2].replace('"','\''),\
                  row[3].replace('"', '\''),row[4].replace('"','\''),\
                  row[5].replace('"', '\''),row[6].replace('"', '\''),\
                  row[7].replace('"', '\''),row[8].replace('"', '\'')))
      pass

   connect.commit()
   pass

#Create the data base with all CSV files
def create_DataBase():
   for fileName in range(0, 11):
      print str('csv file{0}').format(str(fileName))
      dataBase(fileName)
      pass 
   pass

#Create a list with all data from the data base
def dataBase_to_List():
   connect = sqlite3.connect("lp_master.db")
   c = connect.cursor()   
   c.execute('''select * from teachers''')
   #dataList = []
##   count = 0
   for x in c.fetchall():
##      if count == 1000:
##         break
##      count +=1
      dataList.append(x)
      pass
   return dataList
 
##
###Return list
##def get_List(myList):
##   return myList

   
#Create stistic - teacher per year per stablishment
def stats_1():
   sorted_list = []
   
   dataList = dataBase_to_List()
   dataList = sorted(dataList, key=itemgetter(8,6,7,5,4,3,2,1,0))
   countTeachers = 0
   countListIndex = 0
   for i in dataList:
      if len(sorted_list) == 0:
         countTeachers += 1
         sorted_list.insert(countListIndex,( countTeachers, i[6], i[8]))
      else:   
         if sorted_list[countListIndex][1] == i[6]\
            and sorted_list[countListIndex][2] == i[8]:
            countTeachers += 1
            sorted_list.insert(countListIndex,(countTeachers, i[6],\
                                               i[8]))
          
         else:
            countListIndex += 1
            
            sorted_list.insert(countListIndex,(countTeachers, i[6],\
                                               i[8]))
            countTeachers = 1
            pass
         pass
      pass
   return sorted_list

##list1 = stats_1()
##c = 1
##for j in list1:   
##   c += 1
##   print j
##   if c == 1000:
##      break
##   pass


#Create stistics - teachers per category per stablishment per year
def stats_2():
   sorted_list = []
  
   dataList = dataBase_to_List()
   dataList = sorted(dataList, key=itemgetter(4,8,6,7,5,3,2,1,0))
   countTeachers = 0
   countListIndex = 0
   for i in dataList:
 
      if len(sorted_list) == 0:
         countTeachers += 1
         sorted_list.insert(countListIndex,( countTeachers, i[4].encode('ascii', 'ignore'),\
                                             i[8].encode('ascii', 'ignore'),\
                                             i[6].encode('ascii', 'ignore')))
      else:   
         if sorted_list[countListIndex][1].encode('ascii', 'ignore')==i[4].encode('ascii', 'ignore')\
            and sorted_list[countListIndex][2].encode('ascii', 'ignore') == i[6].encode('ascii', 'ignore')\
            and sorted_list[countListIndex][3].encode('ascii', 'ignore') == i[8].encode('ascii', 'ignore'):
            countTeachers += 1
            sorted_list.insert(countListIndex,(countTeachers, i[4].encode('ascii', 'ignore'),\
                                               i[8].encode('ascii', 'ignore'),\
                                               i[6].encode('ascii', 'ignore')))
           
         else:
            countListIndex += 1
            
            sorted_list.insert(countListIndex,(countTeachers,  i[4].encode('ascii', 'ignore'),\
                                               i[8].encode('ascii', 'ignore'),\
                                               i[6].encode('ascii', 'ignore')))
            countTeachers = 1
            pass
         pass
      pass
   return sorted_list 
     

##list2 = stats_2()
##c = 1
##for j in list2:   
##   c += 1
##   print j
##   if c == 10000:
##      break
##   pass
##
##      
   
   
#create statistics - teacher per type of degree per stablishment per year   
def stats_3():
   sorted_list = []
   #list_year = []
   #if len(dataList) == 0:
   dataList = dataBase_to_List()
   dataList = sorted(dataList, key=itemgetter(2,8,6,7,5,4,3,1,0))
   countTeachers = 0
   countListIndex = 0
   for i in dataList:#range(0,1000):
 
      if len(sorted_list) == 0:
         countTeachers += 1
         sorted_list.insert(countListIndex,( countTeachers, i[2], i[8], i[6]))
      else:
         if sorted_list[countListIndex][1] == i[2] and sorted_list[countListIndex][2] == i[8]\
               and sorted_list[countListIndex][3] == i[6]:
            countTeachers += 1
            sorted_list.insert(countListIndex,( countTeachers, i[2], i[8], i[6]))         
         else:
            countListIndex += 1            
            sorted_list.insert(countListIndex,( countTeachers, i[2],i[8],i[6]))
            countTeachers = 1
            pass
         pass
      pass
   return sorted_list

##list3 = stats_3()
##c = 1
##for j in list3:   
##   c += 1
##   print j
##   if c == 1000:
##      break
##   pass

   
